[SQL]レコードから改行を除去する

Yura

2017.06.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データに改行が入っていて、それを取り除きたいということがありました。
データベースに取り込む前に、不要な改行を除去するプログラム書くのも一つの手ではありますが、
SQLを使った方が簡単にできるかと思います。

実行環境

Windows8.1
MySQL5.7
PostgreSQL 9.6

サンプルデータ作成

次のようなテーブルとデータを用意しました。
devdbというスキーマ(db)はすでに作成されているものとします。
改行3種類(LF, CR, CRLF)と改行なしの4パターンです。
commentカラムに入っている改行を除去することを想定します。

CREATE

CREATE TABLE devdb.profile(name varchar (16), comment varchar (100));

INSERT

PostgreSQL版

INSERT 
INTO devdb.profile 
VALUES
('raoh', '初めまして' || chr(10) || 'これからよろしく。'),
('toki', 'どうも。' || chr(13) || '趣味は筋トレです。'),
('jagi', 'お世話になっております。' || chr(13) || chr(10) || '趣味はボウリングです。'),
('kenshiro', 'よろしく、趣味は読書と散歩です。')
;

SELECT * FROM devdb.profile;
   name   |             comment
----------+----------------------------------
 raoh     | 初めまして                      +
          | これからよろしく。
 toki     | どうも。\r趣味は筋トレです。
 jagi     | お世話になっております。\r      +
          | 趣味はボウリングです。
 kenshiro | よろしく、趣味は読書と散歩です。

MySQL版

INSERT 
INTO devdb.profile 
VALUES 
('raoh', '初めまして\nこれからよろしく。'),
('toki', 'どうも。\r趣味は筋トレです。'),
('jagi', 'お世話になっております。\r\n趣味はボウリングです。'),
('kenshiro', 'よろしく、趣味は読書と散歩です。')
;

SELECT * FROM devdb.profile;
+----------+-------------------------------------------------------------------------+
| name     | comment                                                                 |
+----------+-------------------------------------------------------------------------+
| raoh     | 初めまして
これからよろしく。                                           |
趣味は筋トレです。                                             |
| jagi     | お世話になっております。
趣味はボウリングです。                        |
| kenshiro | よろしく、趣味は読書と散歩です。                                        |
+----------+-------------------------------------------------------------------------+

改行を除去する

PostgreSQLの場合

PostgreSQLの場合は、以下のようにregexp_replace関数を使用し正規表現で複数の改行コードを同時に除去することができます。

SELECT regexp_replace(comment, '\r|\n|\r\n', '') AS comment FROM devdb.profile;

これで改行を除去したデータが取得できましたので、応用して既存のレコードを置き替えます。

DROP TABLE IF EXISTS tmp_profile;
CREATE TEMP TABLE tmp_profile AS 
SELECT
  name
  , regexp_replace(comment, '\r|\n|\r\n', '') AS comment
FROM
  devdb.profile
WHERE comment SIMILAR TO '%(\r|\n|\r\n)%';
INSERT INTO devdb.profile SELECT * FROM tmp_profile;
DELETE FROM devdb.profile WHERE comment SIMILAR TO '%(\r|\n|\r\n)%';

2つ目のクエリで対象行のデータから改行を削除したデータでテーブルを作成しています。
3つ目と4つ目のクエリでデータの入れ替えを行います。
これでデータから改行を除去できました。

SELECT* FROM devdb.profile;
   name   |                    comment
----------+------------------------------------------------
 kenshiro | よろしく、趣味は読書と散歩です。
 raoh     | 初めましてこれからよろしく。
 toki     | どうも。趣味は筋トレです。
 jagi     | お世話になっております。趣味はボウリングです。

MySQLの場合

MySQLの場合、regexp_replace関数が使えませんので、そこは変更する必要があります。
今回のサンプルデータのように改行コードが複数ある場合でなければ、regexp_replace使う必要ないんですが。

SELECT replace(replace(replace(comment, '\r\n', ''), '\r', ''), '\n', '') AS comment FROM devdb.profile;

MySQLの場合でも、上記クエリで改行が除去できました。
ここまでできればあとは、やることはPostgreSQLと一緒です。

USE devdb;
DROP TABLE IF EXISTS tmp_profile;
CREATE TEMPORARY TABLE tmp_profile AS 
SELECT
  name
  , replace(replace(replace(comment, '\r\n', ''), '\r', ''), '\n', '') AS comment
FROM
  devdb.profile
WHERE comment REGEXP '%\r\n%|\r|\n';
INSERT INTO devdb.profile SELECT * FROM tmp_profile;
DELETE FROM devdb.profile WHERE comment REGEXP '%\r\n%|\r|\n';

SELECT * FROM devdb.profile;
+----------+-----------------------------------------------------------------------+
| name     | comment                                                               |
+----------+-----------------------------------------------------------------------+
| kenshiro | よろしく、趣味は読書と散歩です。                                      |
| raoh     | 初めましてこれからよろしく。                                          |
| toki     | どうも。趣味は筋トレです。                                            |
| jagi     | お世話になっております。趣味はボウリングです。                        |
+----------+-----------------------------------------------------------------------+